/**
 * 
 */
package dstation.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import dstation.databeans.RobotBean;


/**
 * @author Evan
 *
 */
public class RobotDAO {
	private static String insertString;
	private static String lookupString;
	private static String updateString;
	private static String retrieveString;

	/**
	 * Instantiate a new Robot Data Access Object
	 * @param tableName The name of the Robot table
	 */
	public RobotDAO(String tableName){
		insertString = "insert into "+tableName+" (name, sensors) values ( ?, ?)";
		lookupString = "select * from "+tableName+" where robotid = ?";
		updateString = "update "+tableName+" set name = ? , sensors = ? where robotid = ?";
		retrieveString = "select * from "+tableName;
		try {
			Connection con = DriverManager.getConnection("jdbc:mysql:///colony");
			ResultSet tableNames = con.prepareStatement("SHOW TABLES").executeQuery();
			boolean exists = false;
			while(tableNames.next()){
				String temp = tableNames.getString(1);
				if(temp.equals(tableName)){
					exists = true;
					break;
				}
			}
			if(!exists){
				Statement stmt = con.createStatement();
				stmt.executeUpdate("create table "+tableName+" (robotid int not null auto_increment," +
						" name varchar(100) not null, sensors int not null," +
				" primary key(robotid), unique (name))");
				stmt.close();
			}
			tableNames.close();
			con.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Add a new robot to the table
	 * @param r A bean containing data about the robot to add (id is ignored)
	 * @return The robot that was added
	 * @throws SQLException 
	 */
	public RobotBean insert(RobotBean r) throws SQLException{
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			stmt = con.prepareStatement(insertString, Statement.RETURN_GENERATED_KEYS);
			stmt.setString(1, r.getName());
			stmt.setInt(2, 0);
			stmt.executeUpdate();
			con.commit();
			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			r.setId(rs.getInt(1));
			r.setSensors(0);
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(stmt!=null){stmt.close();}
			if(con!=null){con.close();}
		}
		return r;
	}

	/**
	 * Retrieve data about a robot
	 * @param id The id of the robot to lookup
	 * @return A bean representing the retrieved robot
	 * @throws SQLException 
	 */
	public RobotBean lookup(int id) throws SQLException{
		Connection con = null;
		PreparedStatement stmt = null;
		RobotBean r = new RobotBean();
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			stmt = con.prepareStatement(lookupString);
			stmt.setInt(1, id);
			ResultSet rs = stmt.executeQuery();
			con.commit();
			rs.next();
			r.setId(rs.getInt("robotid"));
			r.setName(rs.getString("name"));
			r.setSensors(rs.getInt("sensors"));
			stmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(stmt!=null){stmt.close();}
			if(con!=null){con.close();}
		}
		return r;
	}

	/**
	 * Retrieve the robot table
	 * @return A bean representing the retrieved robot
	 * @throws SQLException 
	 */
	public List<RobotBean> retrieveAll() throws SQLException{
		Connection con = null;
		PreparedStatement stmt = null;
		List<RobotBean> robots = new ArrayList<RobotBean>();
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			stmt = con.prepareStatement(retrieveString);
			ResultSet rs = stmt.executeQuery();
			con.commit();
			while(rs.next()){
				RobotBean r = new RobotBean();
				r.setId(rs.getInt("robotid"));
				r.setName(rs.getString("name"));
				r.setSensors(rs.getInt("sensors"));
				robots.add(r);
			}
			stmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(stmt!=null){stmt.close();}
			if(con!=null){con.close();}
		}
		return robots;
	}


	/**
	 * Update data about a robot in the table
	 * @param r A bean containing data about the robot to be updated
	 * @throws SQLException 
	 */
	public void update(RobotBean r) throws SQLException{
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			stmt = con.prepareStatement(updateString);
			stmt.setString(1, r.getName());
			stmt.setInt(2, r.getSensors());
			stmt.setInt(3, r.getId());
			stmt.executeUpdate();
			con.commit();
			stmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(stmt!=null){stmt.close();}
			if(con!=null){con.close();}
		}
	}
}
